{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "user ········\n",
      "pwd ····\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "usr = getpass.getpass('user')\n",
    "pwd = getpass.getpass('pwd')\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://$usr:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "Coincidence. Have two guests with the same surname ever stayed in the hotel on the evening? Show the last name and both first names. Do not include duplicates.\n",
    "\n",
    "```\n",
    "+-----------+------------+-------------+\n",
    "| last_name | first_name | first_name  |\n",
    "+-----------+------------+-------------+\n",
    "| Davies    | Philip     | David T. C. |\n",
    "| Evans     | Graham     | Mr Nigel    |\n",
    "| Howarth   | Mr George  | Sir Gerald  |\n",
    "| Jones     | Susan Elan | Mr Marcus   |\n",
    "| Lewis     | Clive      | Dr Julian   |\n",
    "| McDonnell | John       | Dr Alasdair |\n",
    "+-----------+------------+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "6 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>last_name</th>\n",
       "        <th>first_name1</th>\n",
       "        <th>first_name2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Davies</td>\n",
       "        <td>Philip</td>\n",
       "        <td>David T. C.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Evans</td>\n",
       "        <td>Graham</td>\n",
       "        <td>Mr Nigel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Howarth</td>\n",
       "        <td>Mr George</td>\n",
       "        <td>Sir Gerald</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jones</td>\n",
       "        <td>Susan Elan</td>\n",
       "        <td>Mr Marcus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Lewis</td>\n",
       "        <td>Clive</td>\n",
       "        <td>Dr Julian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>McDonnell</td>\n",
       "        <td>John</td>\n",
       "        <td>Dr Alasdair</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Davies', 'Philip', 'David T. C.'),\n",
       " ('Evans', 'Graham', 'Mr Nigel'),\n",
       " ('Howarth', 'Mr George', 'Sir Gerald'),\n",
       " ('Jones', 'Susan Elan', 'Mr Marcus'),\n",
       " ('Lewis', 'Clive', 'Dr Julian'),\n",
       " ('McDonnell', 'John', 'Dr Alasdair')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (SELECT * \n",
    "           FROM guest JOIN booking ON guest.id=booking.guest_id\n",
    ")\n",
    "SELECT DISTINCT b.last_name, b.first_name first_name1, a.first_name first_name2\n",
    "  FROM t a JOIN t b ON a.last_name = b.last_name AND a.first_name <> b.first_name\n",
    "    WHERE (\n",
    "        ((a.booking_date BETWEEN b.booking_date AND \n",
    "          b.booking_date + b.nights-1) OR\n",
    "        -- MySQL: DATE_ADD(b.booking_date, INTERVAL b.nights-1 DAY)\n",
    "         (b.booking_date BETWEEN a.booking_date AND \n",
    "          a.booking_date + a.nights-1))\n",
    "        -- MySQL: DATE_ADD(a.booking_date, INTERVAL a.nights-1 DAY)\n",
    "        AND a.id >= b.id\n",
    "    )\n",
    "    ORDER BY b.last_name\n",
    ";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "Check out per floor. The first digit of the room number indicates the floor – e.g. room 201 is on the 2nd floor. For each day of the week beginning 2016-11-14 show how many rooms are being vacated that day by floor number. Show all days in the correct order.\n",
    "\n",
    "```\n",
    "+------------+-----+-----+-----+\n",
    "| i          | 1st | 2nd | 3rd |\n",
    "+------------+-----+-----+-----+\n",
    "| 2016-11-14 |   5 |   3 |   4 |\n",
    "| 2016-11-15 |   6 |   4 |   1 |\n",
    "| 2016-11-16 |   2 |   2 |   4 |\n",
    "| 2016-11-17 |   5 |   3 |   6 |\n",
    "| 2016-11-18 |   2 |   3 |   2 |\n",
    "| 2016-11-19 |   5 |   5 |   1 |\n",
    "| 2016-11-20 |   2 |   2 |   2 |\n",
    "+------------+-----+-----+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>i</th>\n",
       "        <th>1st</th>\n",
       "        <th>2nd</th>\n",
       "        <th>3rd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-14</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-15</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-16</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-17</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-18</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-19</td>\n",
       "        <td>5</td>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-20</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2016, 11, 14), 5, 3, 4),\n",
       " (datetime.date(2016, 11, 15), 6, 4, 1),\n",
       " (datetime.date(2016, 11, 16), 2, 2, 4),\n",
       " (datetime.date(2016, 11, 17), 5, 3, 6),\n",
       " (datetime.date(2016, 11, 18), 2, 3, 2),\n",
       " (datetime.date(2016, 11, 19), 5, 5, 1),\n",
       " (datetime.date(2016, 11, 20), 2, 2, 2)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- [MySQL Solution]\n",
    "SELECT checkout_date i, \n",
    "    SUM(CASE WHEN floor='1' THEN 1 ELSE 0 END) AS \"1st\", \n",
    "    SUM(CASE WHEN floor='2' THEN 1 ELSE 0 END) AS \"2nd\", \n",
    "    SUM(CASE WHEN floor='3' THEN 1 ELSE 0 END) AS \"3rd\"\n",
    "FROM \n",
    "(SELECT booking_date + nights checkout_date, LEFT(''||room_no, 1) floor \n",
    " -- MySQL: LEFT(room_no, 1) floor\n",
    "   FROM booking \n",
    "   WHERE booking_date + nights BETWEEN '2016-11-14' AND\n",
    "     date '2016-11-14' + 6\n",
    " -- MySQL: DATE_ADD()\n",
    ") AS t\n",
    "GROUP BY checkout_date;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "Done.\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>i</th>\n",
       "        <th>1st</th>\n",
       "        <th>2nd</th>\n",
       "        <th>3rd</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-14</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-15</td>\n",
       "        <td>6</td>\n",
       "        <td>4</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-16</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-17</td>\n",
       "        <td>5</td>\n",
       "        <td>3</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-18</td>\n",
       "        <td>2</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-19</td>\n",
       "        <td>5</td>\n",
       "        <td>5</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-20</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2016, 11, 14), 5, 3, 4),\n",
       " (datetime.date(2016, 11, 15), 6, 4, 1),\n",
       " (datetime.date(2016, 11, 16), 2, 2, 4),\n",
       " (datetime.date(2016, 11, 17), 5, 3, 6),\n",
       " (datetime.date(2016, 11, 18), 2, 3, 2),\n",
       " (datetime.date(2016, 11, 19), 5, 5, 1),\n",
       " (datetime.date(2016, 11, 20), 2, 2, 2)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- [Pivot Solution]\n",
    "CREATE EXTENSION IF NOT EXISTS tablefunc;\n",
    "SELECT * FROM\n",
    "  crosstab(\n",
    "    'SELECT booking_date + nights checkout, LEFT(''''||room_no, 1) floor, COUNT(booking_id) n\n",
    "      FROM booking \n",
    "        WHERE booking_date + nights BETWEEN ''2016-11-14'' AND date ''2016-11-14'' + 6\n",
    "        GROUP BY booking_date + nights, LEFT(''''||room_no, 1)'\n",
    "  ) AS ct(i date, \"1st\" bigint, \"2nd\" bigint, \"3rd\" bigint);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "Free rooms? List the rooms that are free on the day 25th Nov 2016.\n",
    "\n",
    "```\n",
    "+-----+\n",
    "| id  |\n",
    "+-----+\n",
    "| 207 |\n",
    "| 210 |\n",
    "| 304 |\n",
    "+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>304</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(207,), (210,), (304,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id FROM room WHERE id NOT IN\n",
    "(SELECT room_no FROM booking \n",
    " WHERE booking_date <= '2016-11-25' AND booking_date+nights-1 >= '2016-11-25');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "Single room for three nights required. A customer wants a single room for three consecutive nights. Find the first available date in December 2016.\n",
    "\n",
    "```\n",
    "+-----+------------+\n",
    "| id  | MIN(i)     |\n",
    "+-----+------------+\n",
    "| 201 | 2016-12-11 |\n",
    "+-----+------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "8 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>room_no</th>\n",
       "        <th>this_booking</th>\n",
       "        <th>this_nights</th>\n",
       "        <th>next_booking</th>\n",
       "        <th>diff</th>\n",
       "        <th>next_nights</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2016-12-03</td>\n",
       "        <td>5</td>\n",
       "        <td>2016-12-08</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2016-12-08</td>\n",
       "        <td>2</td>\n",
       "        <td>2016-12-10</td>\n",
       "        <td>0</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>101</td>\n",
       "        <td>2016-12-10</td>\n",
       "        <td>5</td>\n",
       "        <td>2016-12-15</td>\n",
       "        <td>0</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>2016-12-01</td>\n",
       "        <td>2</td>\n",
       "        <td>2016-12-03</td>\n",
       "        <td>0</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>2016-12-03</td>\n",
       "        <td>4</td>\n",
       "        <td>2016-12-07</td>\n",
       "        <td>0</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>2016-12-02</td>\n",
       "        <td>2</td>\n",
       "        <td>2016-12-04</td>\n",
       "        <td>0</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>2016-12-04</td>\n",
       "        <td>1</td>\n",
       "        <td>2016-12-05</td>\n",
       "        <td>0</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>301</td>\n",
       "        <td>2016-12-05</td>\n",
       "        <td>5</td>\n",
       "        <td>2016-12-12</td>\n",
       "        <td>2</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(101, datetime.date(2016, 12, 3), 5, datetime.date(2016, 12, 8), 0, 2),\n",
       " (101, datetime.date(2016, 12, 8), 2, datetime.date(2016, 12, 10), 0, 5),\n",
       " (101, datetime.date(2016, 12, 10), 5, datetime.date(2016, 12, 15), 0, 3),\n",
       " (201, datetime.date(2016, 12, 1), 2, datetime.date(2016, 12, 3), 0, 4),\n",
       " (201, datetime.date(2016, 12, 3), 4, datetime.date(2016, 12, 7), 0, 4),\n",
       " (301, datetime.date(2016, 12, 2), 2, datetime.date(2016, 12, 4), 0, 1),\n",
       " (301, datetime.date(2016, 12, 4), 1, datetime.date(2016, 12, 5), 0, 5),\n",
       " (301, datetime.date(2016, 12, 5), 5, datetime.date(2016, 12, 12), 2, 1)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- First, build a query to show the bookings with the next ones\n",
    "WITH t AS (\n",
    "    SELECT a.room_no, a.booking_date this_booking, a.nights this_nights, \n",
    "      MIN(b.booking_date) AS next_booking, \n",
    "      MIN(b.booking_date) - a.booking_date - a.nights AS diff\n",
    "    FROM booking a LEFT JOIN booking b ON a.booking_date < b.booking_date AND a.room_no=b.room_no\n",
    "    WHERE a.room_no IN (SELECT id FROM room WHERE room_type='single') AND\n",
    "      TO_CHAR(a.booking_date, 'YYYYMM')='201612' AND TO_CHAR(b.booking_date, 'YYYYMM')='201612'\n",
    "    GROUP BY a.room_no, a.nights, a.booking_date\n",
    "    ORDER BY a.room_no, a.booking_date\n",
    ")\n",
    "\n",
    "SELECT t.*, booking.next_nights FROM \n",
    "  t LEFT JOIN (SELECT nights next_nights, booking_date, room_no FROM booking) booking ON \n",
    "    (t.next_booking=booking.booking_date AND booking.room_no=t.room_no)\n",
    "    ORDER BY room_no, this_booking;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>room_no</th>\n",
       "        <th>i</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>201</td>\n",
       "        <td>2016-12-11</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(201, datetime.date(2016, 12, 11))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- Then, use this query to filter diff>3 or \n",
    "--   last checkout date is >3 days prior to end of month.\n",
    "-- Complete solution:\n",
    "WITH t AS (\n",
    "    SELECT a.room_no, a.booking_date this_booking, a.nights this_nights, \n",
    "      MIN(b.booking_date) AS next_booking, \n",
    "      MIN(b.booking_date) - a.booking_date - a.nights AS diff\n",
    "    FROM booking a LEFT JOIN booking b ON (a.booking_date < b.booking_date AND\n",
    "                                           a.room_no=b.room_no)\n",
    "    WHERE a.room_no IN (SELECT id FROM room WHERE room_type='single') AND\n",
    "      TO_CHAR(a.booking_date, 'YYYYMM')='201612' AND \n",
    "      TO_CHAR(b.booking_date, 'YYYYMM')='201612'\n",
    "    GROUP BY a.room_no, a.nights, a.booking_date\n",
    "    ORDER BY a.room_no, a.booking_date\n",
    "), tt AS (\n",
    "    SELECT t.*, booking.next_nights FROM \n",
    "      t LEFT JOIN (\n",
    "          SELECT nights next_nights, booking_date, room_no FROM booking\n",
    "      ) booking ON \n",
    "        (t.next_booking=booking.booking_date AND booking.room_no=t.room_no)\n",
    "        ORDER BY room_no, this_booking\n",
    ")\n",
    "\n",
    "SELECT * FROM (\n",
    "    SELECT room_no, next_booking+next_nights i FROM tt WHERE diff >=3 UNION\n",
    "    SELECT room_no, MAX(next_booking+next_nights) i FROM tt \n",
    "        GROUP BY room_no\n",
    "        HAVING DATE_PART('day', MAX(next_booking+next_nights)) < 31-3) c\n",
    "    ORDER BY c.i\n",
    "    LIMIT 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "Gross income by week. Money is collected from guests when they leave. For each Thursday in November and December 2016, show the total amount of money collected from the previous Friday to that day, inclusive.\n",
    "\n",
    "```\n",
    "+------------+---------------+\n",
    "| Thursday   | weekly_income |\n",
    "+------------+---------------+\n",
    "| 2016-11-03 |          0.00 |\n",
    "| 2016-11-10 |      12608.94 |\n",
    "| 2016-11-17 |      13552.56 |\n",
    "| 2016-11-24 |      12929.69 |\n",
    "| 2016-12-01 |      11685.14 |\n",
    "| 2016-12-08 |      13093.79 |\n",
    "| 2016-12-15 |       8975.87 |\n",
    "| 2016-12-22 |       1395.77 |\n",
    "| 2016-12-29 |          0.00 |\n",
    "| 2017-01-05 |          0.00 |\n",
    "+------------+---------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://madlogos:***@localhost/sqlzoo\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Thursday</th>\n",
       "        <th>weekly_income</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-03</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-10</td>\n",
       "        <td>12608.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-17</td>\n",
       "        <td>13552.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-11-24</td>\n",
       "        <td>12929.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-01</td>\n",
       "        <td>11685.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-08</td>\n",
       "        <td>13093.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-15</td>\n",
       "        <td>8975.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-22</td>\n",
       "        <td>1395.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2016-12-29</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2017-01-05</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.date(2016, 11, 3), Decimal('0')),\n",
       " (datetime.date(2016, 11, 10), Decimal('12608.94')),\n",
       " (datetime.date(2016, 11, 17), Decimal('13552.56')),\n",
       " (datetime.date(2016, 11, 24), Decimal('12929.69')),\n",
       " (datetime.date(2016, 12, 1), Decimal('11685.14')),\n",
       " (datetime.date(2016, 12, 8), Decimal('13093.79')),\n",
       " (datetime.date(2016, 12, 15), Decimal('8975.87')),\n",
       " (datetime.date(2016, 12, 22), Decimal('1395.77')),\n",
       " (datetime.date(2016, 12, 29), Decimal('0')),\n",
       " (datetime.date(2017, 1, 5), Decimal('0'))]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- income: gross income by day\n",
    "-- thursdays: Thursdays in Nov & Dec\n",
    "WITH income AS (\n",
    "    SELECT a.checkout, SUM(COALESCE(a.amount, 0)) amount FROM\n",
    "      (SELECT booking_date, booking_date+nights checkout,\n",
    "        SUM(rate.amount * booking.nights) amount\n",
    "        FROM booking LEFT JOIN rate ON (\n",
    "          booking.occupants=rate.occupancy AND \n",
    "          booking.room_type_requested=rate.room_type)\n",
    "        GROUP BY booking_date, booking_date+nights\n",
    "       UNION\n",
    "       SELECT booking_date, booking_date+nights checkout,\n",
    "           SUM(extra.amount) amount\n",
    "           FROM booking LEFT JOIN extra ON (\n",
    "                booking.booking_id=extra.booking_id)\n",
    "           GROUP BY booking.booking_date, booking_date+nights\n",
    "      ) AS a\n",
    "    GROUP BY a.checkout\n",
    "    ORDER BY a.checkout\n",
    "), thursdays AS (\n",
    "    SELECT DISTINCT GENERATE_SERIES(\n",
    "      date '2016-11-1'+ 4-extract(dow FROM date '2016-11-1')::integer, \n",
    "      date '2016-12-31' + 11-extract(dow FROM date '2016-12-31')::integer, '1 week')::date AS thu\n",
    "    FROM room_type\n",
    "    ORDER BY thu\n",
    ")\n",
    "\n",
    "SELECT thursdays.thu \"Thursday\", SUM(COALESCE(income.amount, 0)) weekly_income\n",
    "  FROM income RIGHT JOIN thursdays ON income.checkout BETWEEN thursdays.thu - 6 AND thursdays.thu \n",
    "    GROUP BY thursdays.thu\n",
    "    ORDER BY thursdays.thu;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
